/**
 * 
 */
package com.autoscript.ext.xmldata.database.process;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.swing.JLabel;

import com.autoscript.ext.ISystemConstant;
import com.autoscript.ext.xmldata.ProcessInfo;
import com.autoscript.ext.xmldata.database.dbmodelext.ColumnModelExt;
import com.autoscript.ext.xmldata.database.dbmodelext.IColumnModelExt;
import com.autoscript.ext.xmldata.database.dbmodelext.IIndexModelExt;
import com.autoscript.ext.xmldata.database.dbmodelext.ITableModelExt;
import com.autoscript.ext.xmldata.database.dbmodelext.IndexModelExt;
import com.autoscript.ext.xmldata.database.dbmodelext.TableModelExt;
import com.autoscript.ext.xmldata.database.model.datasource.IDataSourceItemModel;
import com.autoscript.ext.xmldata.dbmodel.DBModel;
import com.autoscript.ext.xmldata.dbmodel.IColumnModel;
import com.autoscript.ext.xmldata.dbmodel.IDBModel;
import com.autoscript.ext.xmldata.dbmodel.IIndexModel;
import com.autoscript.ext.xmldata.dbmodel.IKeyModel;
import com.autoscript.ext.xmldata.dbmodel.ITableModel;
import com.autoscript.ext.xmldata.dbmodel.KeyModel;
import com.autoscript.ui.helper.DriverHelper;
import com.autoscript.ui.helper.StringHelper;

/**
 * 数据源处理器实现 作者:龙色波 日期:2013-10-29
 */
public abstract class AbstractDataSourceProcess implements IDataSourceProcess {
	/**
	 * 数据库连接
	 */
	protected Connection con;
	private ProcessInfo processMessage;

	/*
	 * (non-Javadoc)
	 * 
	 * @seecom.autoscript.ext.xmldata.database.process.IDataSourceProcess#
	 * fillTabModelInfo
	 * (com.autoscript.ext.xmldata.database.model.datasource.IDataSourceItemModel
	 * , com.autoscript.ext.xmldata.database.dbmodelext.ITableModelExt)
	 */
	@Override
	public List<ITableModel> fillTabModelsInfo(
			IDataSourceItemModel dsItemModel, List<ITableModel> tableModels)
			throws Exception {

		IKeyModel keyModel;
		// 获取数据库连接
		con = null;
		try {
			con = getConnect(dsItemModel);
			for (ITableModel tableModel : tableModels) {
				// 构造列模型
				List<IColumnModel> columnModels;
				columnModels = makeColumnModel((ITableModelExt) tableModel, con);
				tableModel.setColumnModels(columnModels);
				// 构造主键模型
				keyModel = makeKeyModel((ITableModelExt) tableModel, con);
				tableModel.setKey(keyModel);
				// 构造索引模型
				List<IIndexModel> indexModels;
				indexModels = makeIndexModel((ITableModelExt) tableModel, con);
				tableModel.setIndexModels(indexModels);
			}
			return tableModels;
		} finally {
			// 关闭数据库连接
			if (con != null) {
				con.close();
			}
		}
	}

	/**
	 * 构造列模型列表
	 * 
	 * @param tableModel
	 * @param con
	 * @return
	 * @throws SQLException
	 */
	private List<IColumnModel> makeColumnModel(ITableModelExt tableModel,
			Connection con) throws SQLException {
		DatabaseMetaData metaData;
		metaData = con.getMetaData();
		List<IColumnModel> columnModels = new ArrayList<IColumnModel>();
		ResultSet rs = null;
		try {
			rs = metaData.getColumns(con.getCatalog(), tableModel
					.getTableSchem(), tableModel.getCode(), null);
			ResultSetMetaData rsmd = rs.getMetaData();
			Map<String,String> columnCommentsMap = getColumnComments(tableModel.getTableSchem(), tableModel.getCode());
			while (rs.next()) {
				IColumnModelExt columnModel = new ColumnModelExt();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					String columnName;
					columnName = rsmd.getColumnName(i);
					if (ISystemConstant.CHAR_OCTET_LENGTH.equals(columnName)) {
						columnModel.setCharOctetLength(rs
								.getInt(ISystemConstant.CHAR_OCTET_LENGTH));
					} else if (ISystemConstant.COLUMN_NAME.equals(columnName)) {
						columnModel.setCode(rs
								.getString(ISystemConstant.COLUMN_NAME));
					} else if (ISystemConstant.COLUMN_DEF.equals(columnName)) {
						columnModel.setColumnDef(rs
								.getString(ISystemConstant.COLUMN_DEF));
					} else if (ISystemConstant.TYPE_NAME.equals(columnName)) {
						columnModel.setDataType(rs
								.getString(ISystemConstant.TYPE_NAME));
					} else if (ISystemConstant.IS_AUTOINCREMENT
							.equals(columnName)) {
						columnModel.setIsAutoincrement(rs
								.getString(ISystemConstant.IS_AUTOINCREMENT));
					} else if (ISystemConstant.IS_NULLABLE.equals(columnName)) {
						columnModel.setIsNullable(rs
								.getString(ISystemConstant.IS_NULLABLE));
					} else if (ISystemConstant.DATA_TYPE.equals(columnName)) {
						columnModel.setJavaDataType(rs
								.getInt(ISystemConstant.DATA_TYPE));
					} else if (ISystemConstant.COLUMN_SIZE.equals(columnName)) {
						columnModel.setLength(rs
								.getInt(ISystemConstant.COLUMN_SIZE));
					} else if (ISystemConstant.NULLABLE.equals(columnName)) {
						if (rs.getInt(ISystemConstant.NULLABLE) != DatabaseMetaData.columnNullable) {
							columnModel.setMandatory(true);
						} else {
							columnModel.setMandatory(false);
						}
					} else if (ISystemConstant.REMARKS.equals(columnName)) {
						columnModel.setName(rs
								.getString(ISystemConstant.REMARKS));
					} else if (ISystemConstant.NUM_PREC_RADIX
							.equals(columnName)) {
						columnModel.setNumPrecRadix(rs
								.getInt(ISystemConstant.NUM_PREC_RADIX));
					} else if (ISystemConstant.ORDINAL_POSITION
							.equals(columnName)) {
						columnModel.setOrdinalPosition(rs
								.getInt(ISystemConstant.ORDINAL_POSITION));
					} else if (ISystemConstant.DECIMAL_DIGITS
							.equals(columnName)) {
						columnModel.setPrecision(rs
								.getInt(ISystemConstant.DECIMAL_DIGITS));
					}
					//设置注释为字段中文名
				    columnModel.setName(columnCommentsMap.get(columnModel.getCode()));
				}
				columnModels.add(columnModel);
			}
			
			return columnModels;
		} finally {
			if (rs != null) {
				rs.close();
			}

		}
	}

	/**
	 * 构造索引模型
	 * 
	 * @param tableModel
	 *            表模型
	 * @param con
	 *            数据库元数据
	 * @return
	 * @throws SQLException
	 */
	private List<IIndexModel> makeIndexModel(ITableModelExt tableModel,
			Connection con) throws SQLException {
		DatabaseMetaData metaData;
		metaData = con.getMetaData();
		List<IIndexModel> indexModels = new ArrayList<IIndexModel>();
		ResultSet rs = null;
		try {
			rs = metaData.getIndexInfo(con.getCatalog(), tableModel
					.getTableSchem(), tableModel.getCode(), false, false);
			String indexName = " ";
			IIndexModelExt indexModel = null;
			List<String> columnNames = null;
			while (rs.next()) {
				if(rs.getString(ISystemConstant.INDEX_NAME)!=null){
					if (!indexName.equals(rs.getString(ISystemConstant.INDEX_NAME))) {
						// 上一个索引模型加入到列表
						if (indexModel != null) {
							// 设置列名列表
							indexModel.setColumnNames(columnNames);
							indexModels.add(indexModel);
						}
						indexModel = new IndexModelExt();
						columnNames = new ArrayList<String>();
						indexName = rs.getString(ISystemConstant.INDEX_NAME);
						indexModel.setCode(indexName);
						indexModel.setAscOrDesc(rs
								.getString(ISystemConstant.ASC_OR_DESC));
						indexModel.setIndexQualifier(rs
								.getString(ISystemConstant.INDEX_QUALIFIER));
						indexModel.setName(indexModel.getCode());
						indexModel.setNonUnique(rs
								.getBoolean(ISystemConstant.NON_UNIQUE));
						indexModel.setType(rs.getString(ISystemConstant.TYPE));
					}
					columnNames.add(rs.getString(ISystemConstant.COLUMN_NAME));
				}
			}
			// 加入最后一个索引模型
			if (indexModel != null) {
				// 设置列名列表
				indexModel.setColumnNames(columnNames);
				indexModels.add(indexModel);
			}
			return indexModels;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}

	/**
	 * 构造主键模型
	 * 
	 * @param tableModel
	 *            表模型
	 * @param metaData
	 *            数据库元数据
	 * @return
	 * @throws SQLException
	 */
	private IKeyModel makeKeyModel(ITableModelExt tableModel, Connection con)
			throws SQLException {
		KeyModel keyModel = null;
		ResultSet rs = null;
		DatabaseMetaData metaData;
		metaData = con.getMetaData();
		keyModel = new KeyModel();
		List<String> columnNames = new ArrayList<String>();
		try {
			rs = metaData.getPrimaryKeys(con.getCatalog(), tableModel
					.getTableSchem(), tableModel.getCode());
			while (rs.next()) {
				if (StringHelper.isEmpty(keyModel.getCode())) {
					keyModel.setCode(rs.getString(ISystemConstant.PK_NAME));
				}
				columnNames.add(rs.getString(ISystemConstant.COLUMN_NAME));
			}
			keyModel.setColumns(columnNames);
			return keyModel;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.autoscript.ext.xmldata.database.process.IDataSourceProcess#parse(
	 * com.autoscript
	 * .ext.xmldata.database.model.datasource.IDataSourceItemModel)
	 */
	@Override
	public IDBModel parse(IDataSourceItemModel dsItemModel) throws Exception {
		// 获取数据库连接
		try {
			dispRunInfo("正在连接数据库...");
			con = getConnect(dsItemModel);
			IDBModel dbModel = new DBModel();
			List<ITableModel> tabModels = null;
			tabModels = makeTableModels(con);
			if (tabModels != null) {
				dbModel.setTableModels(tabModels);
			}
			return dbModel;
		} finally {
			// 关闭数据库连接
			if (con != null) {
				con.close();
			}
		}
	}

	/**
	 * 根据数据库元数据构造表模型列表
	 * 
	 * @param con
	 * @param metaData
	 *            数据库元数据
	 * @return
	 * @throws SQLException
	 */
	private List<ITableModel> makeTableModels(Connection con)
			throws SQLException {
		DatabaseMetaData metaData;
		List<ITableModel> tableModels = new ArrayList<ITableModel>();
		metaData = con.getMetaData();
		// 获取包含"table"关键字的表类型
		String tabTypes[];
		tabTypes = getTableTypes(metaData);
		ResultSet rs = null;
		int tabcount = 0;
		try {
			rs = metaData.getTables(con.getCatalog(), null, null, tabTypes);
			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				ITableModelExt tableModel = new TableModelExt();
				tabcount = tabcount+1;
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					String columnName = rsmd.getColumnName(i);

					if (columnName.equals(ISystemConstant.TABLE_NAME)) {
						
						
						tableModel.setCode(rs
								.getString(ISystemConstant.TABLE_NAME));
						dispRunInfo(String.format("已经解析%1$d个表定义,正在解析:%2$s...", tabcount-1,tableModel.getCode()));
					} else if (columnName
							.equals(ISystemConstant.REF_GENERATION)) {
						tableModel.setRefGeneration(rs
								.getString(ISystemConstant.REF_GENERATION));
					} else if (columnName
							.equals(ISystemConstant.SELF_REFERENCING_COL_NAME)) {
						tableModel
								.setSelfReferencingColName(rs
										.getString(ISystemConstant.SELF_REFERENCING_COL_NAME));
					} else if (columnName.equals(ISystemConstant.TABLE_CAT)) {
						tableModel.setTableCat(rs
								.getString(ISystemConstant.TABLE_CAT));
					} else if (columnName.equals(ISystemConstant.TABLE_SCHEM)) {
						tableModel.setTableSchem(rs
								.getString(ISystemConstant.TABLE_SCHEM));
					} else if (columnName.equals(ISystemConstant.TABLE_TYPE)) {
						tableModel.setTableType(rs
								.getString(ISystemConstant.TABLE_TYPE));
					} else if (columnName.equals(ISystemConstant.TYPE_CAT)) {
						tableModel.setTypeCat(rs
								.getString(ISystemConstant.TYPE_CAT));
					} else if (columnName.equals(ISystemConstant.TYPE_NAME)) {
						tableModel.setTypeName(rs
								.getString(ISystemConstant.TYPE_NAME));
					} else if (columnName.equals(ISystemConstant.TYPE_SCHEM)) {
						tableModel.setTypeSchem(rs
								.getString(ISystemConstant.TYPE_SCHEM));
					}
				}
				// 设置表注释为表中文名
				tableModel.setName(getTableComments(tableModel.getTableSchem(),
						tableModel.getCode()));
				tableModels.add(tableModel);
				try {
					Thread.sleep(5);
				} catch (InterruptedException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			return tableModels;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	/**
	 * 显示执行信息
	 * @param format
	 */
	private void dispRunInfo(String format) {
		if(processMessage!=null){
			processMessage.setMessage(format);
		}
	}

	/**
	 * 获取包含"table"关键字的表类型数组
	 * 
	 * @param metaData
	 * @return
	 * @throws SQLException
	 */
	private String[] getTableTypes(DatabaseMetaData metaData)
			throws SQLException {
		List<String> tableTypes = new ArrayList<String>();
		ResultSet rs = null;

		try {
			rs = metaData.getTableTypes();
			while (rs.next()) {
				String tableType;
				tableType = rs.getString(1);
				if (tableType.toLowerCase().indexOf("table") != -1) {
					tableTypes.add(tableType);
				}
			}
			return tableTypes.toArray(new String[tableTypes.size()]);
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}

	/**
	 * 获取数据库连接
	 * 
	 * @param dsItemModel
	 * @return
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 */
	private Connection getConnect(IDataSourceItemModel dsItemModel)
			throws Exception {
		Class cd;
		Driver d = null;
		Properties info;
		info = new Properties();
		info.put("user", dsItemModel.getUser());
		info.put("password", dsItemModel.getPassword());
		// 按照jar文件列表，顺序尝试
		for (String fileName : dsItemModel.getJarFiles()) {
			cd = DriverHelper
					.getDynamic(fileName, dsItemModel.getDriverClass());
			if (cd != null) {
				d = (Driver) cd.newInstance();

				return d.connect(dsItemModel.getURL(), info);
			}
		}
		// 检索完所有jar文件，依然没有找到，则抛出异常
		throw new Exception("在给定的jar文件列表中，没找到驱动类："
				+ dsItemModel.getDriverClass());

	}

	/**
	 * 获取表注释
	 * 
	 * @param owner
	 *            表的拥有者 通常为schema
	 * @param tableName
	 *            表名
	 * @return 表注释
	 * @throws SQLException
	 */
	public abstract String getTableComments(String owner, String tableName)
			throws SQLException;

	/**
	 * 获取列注释
	 * 
	 * @param owner
	 *            表的拥有者 通常为schema
	 * @param tableName
	 *            表名
	 * @return 列注释 map 列名为key,注释为value
	 * @throws SQLException 
	 */
	public abstract Map<String, String> getColumnComments(String owner,
			String tableName) throws SQLException;
	/**
	 * 设置显示执行过程的信息
	 * @param loadTableLable
	 */
	public void setDispMsg(ProcessInfo processMessage){
		this.processMessage = processMessage;
	}
	
}
